package com.ops.uitl.jdbc;

import java.sql.Connection;
import com.ops.uitl.data.DataMap;
import com.ops.uitl.data.DatasetList;
import com.ops.uitl.data.IData;
import com.ops.uitl.data.IDataset;
import com.ops.uitl.tapestry.BaseData;

public class BaseEntity extends DaoManager {
	
	protected BaseData bd;
	
	/**
	 * construct function
	 * @param bd
	 * @throws Exception
	 */
	public BaseEntity(BaseData bd) throws Exception {
		super(bd.getDBConn());
		this.bd = bd;
	}
	
	/**
	 * construct function
	 * @param bd
	 * @param connName
	 * @throws Exception
	 */
	public BaseEntity(BaseData bd, String connName) throws Exception {
		super(bd.getDBConn(connName));
		this.bd = bd;
	}
	
	/**
	 * construct function
	 * @param bd
	 * @param connNames
	 * @throws Exception
	 */
	public BaseEntity(BaseData bd, String[] connNames) throws Exception {
		this(bd, connNames, BaseFactory.MULTICONN_TYPE_CONNNAME, null);
		this.bd = bd;
	}
	
	/**
	 * construct function
	 * @param bd
	 * @param connName
	 * @throws Exception
	 */
	public BaseEntity(BaseData bd, String connName, String connType) throws Exception {
		super(bd.getDBConn(BaseFactory.CONNECTION_TYPE_DATABASE.equals(connType) ? ConnectionManager.getInstance().getDefaultNameByDatabase(bd.getRouteEparchy() != null ? bd.getRouteEparchy() : bd.getContext().getTradeEpachyId(), connName) : connName));
		this.bd = bd;
	}
	
	/**
	 * construct function
	 * names is null get all conns
	 * @param bd
	 * @param names
	 * @throws Exception
	 */
	public BaseEntity(BaseData bd, String[] names, String mode) throws Exception {
		this(bd, names, mode, "eparchy");
		this.bd = bd;
	}
	
	/**
	 * construct function
	 * names:conns or eparchys
	 * mode:BaseFactory.MULTICONN_TYPETYPE_CONNNAME or BaseFactory.MULTICONN_TYPE_EPARCHY
	 * connColl:multi database,default value eparchy
	 * @param bd
	 * @param names
	 * @param mode
	 * @param connType
	 * @throws Exception
	 */
	public BaseEntity(BaseData bd, String[] names, String mode, String connColl) throws Exception {
		super(getConnections(bd, names, mode, connColl));
		this.bd = bd;
	}
	
	/**
	 * get connnections
	 * @param bd
	 * @param names
	 * @param mode
	 * @param connColl
	 * @return Connection[]
	 * @throws Exception
	 */
	private static Connection[] getConnections(BaseData bd, String[] names, String mode, String connColl) throws Exception {
		String[] connNames = null;
		if (BaseFactory.MULTICONN_TYPE_CONNNAME.equals(mode)) {
			connNames = names;
		}
		if (BaseFactory.MULTICONN_TYPE_EPARCHY.equals(mode)) {
			if (names == null) {
				connNames = ConnectionManager.getInstance().getAllNames(connColl);	
			} else {
				connNames = ConnectionManager.getInstance().getNames(names, connColl);
			}
		}
		Connection[] conns = new Connection[connNames.length];
		for (int i=0; i<conns.length; i++) {
			conns[i] = bd.getDBConn(connNames[i]);
		}
		return conns;
	}
	
	/**
	 * get base data
	 * @return BaseData
	 * @throws Exception
	 */
	public BaseData getBaseData() throws Exception {
		return bd;
	}
	
	/**
	 * get sequence value
	 * @param sequence
	 * @return String
	 * @throws Exception
	 */
	public String getSequenceByEparchy(String sequence) throws Exception {
		return getSequence(bd.getContext().getEpachyId(), sequence);
	}
	
	/**
	 * get sequence values
	 * @param sequence
	 * @return String[]
	 * @throws Exception
	 */
	public String[] getSequenceByEparchys(String sequence) throws Exception {
		return getSequences(bd.getContext().getEpachyId(), sequence);
	}
	
	/** 
	 * query list
	 * @param sql
	 * @param pagination
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(String sql, Pagination pagination) throws Exception {
		return queryList(sql, (Parameter) null, pagination);
	}
	
	/** 
	 * query lists
	 * @param sql
	 * @param pagination
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(String sql, Pagination pagination) throws Exception {
		return queryLists(sql, (Parameter) null, pagination);
	}

	/** 
	 * query list
	 * @param sql
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(String sql, Object[] param) throws Exception {
		return queryList(sql, param, null);
	}
	
	/** 
	 * query lists
	 * @param sql
	 * @param param
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(String sql, Object[] param) throws Exception {
		return queryLists(sql, param, null);
	}
	
	/** 
	 * query list
	 * @param sql
	 * @param param
	 * @param pagination
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(String sql, Object[] param, Pagination pagination) throws Exception {
		return queryList(sql, new Parameter(param), pagination);
	}
	
	/** 
	 * query lists
	 * @param sql
	 * @param param
	 * @param pagination
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(String sql, Object[] param, Pagination pagination) throws Exception {
		return queryLists(sql, new Parameter(param), pagination);
	}
	
	/** 
	 * query list
	 * @param parser
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(SQLParser parser) throws Exception {
		return queryList(parser, null);
	}
	
	/** 
	 * query lists
	 * @param parser
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(SQLParser parser) throws Exception {
		return queryLists(parser, null);
	}
	
	/** 
	 * query list
	 * @param parser
	 * @param 
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryList(SQLParser parser, Pagination pagination) throws Exception {
		return queryList(parser.getSQL(), parser.getParam(), pagination);
	}
	
	/** 
	 * query lists
	 * @param parser
	 * @param 
	 * @return IDataset[]
	 * @throws Exception
	 */
	public IDataset[] queryLists(SQLParser parser, Pagination pagination) throws Exception {
		return queryLists(parser.getSQL(), parser.getParam(), pagination);
	}
	
	/** 
	 * execute update
	 * @param parser
	 * @param int
	 * @throws Exception
	 */
	public int executeUpdate(SQLParser parser) throws Exception {
		return executeUpdate(parser.getSQL(), parser.getParam());
	}
	
	/** 
	 * execute updates
	 * @param parser
	 * @param int[]
	 * @throws Exception
	 */
	public int[] executeUpdates(SQLParser parser) throws Exception {
		return executeUpdates(parser.getSQL(), parser.getParam());
	}
	
	/** 
	 * query lists by merge
	 * @param sql
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryListsByMerge(String sql, Object[] param) throws Exception {
		return queryListsByMerge(sql, new Parameter(param));
	}
	
	/** 
	 * query lists by merge
	 * @param sql
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryListsByMerge(String sql, Parameter param) throws Exception {
		IDataset alldataset = new DatasetList();
		IDataset[] datasets = queryLists(sql, param);
		for (int i=0; i<datasets.length; i++) {
			alldataset.addAll(datasets[i]);
		}
		return alldataset;
	}
	
	/** 
	 * query lists by merge
	 * @param parser
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryListsByMerge(SQLParser parser) throws Exception {
		return queryListsByMerge(parser.getSQL(), parser.getParam());
	}

	/** 
	 * query lists by merge
	 * @param sql
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
	public IDataset queryListsByMerge(String sql, IData param) throws Exception {
		IDataset alldataset = new DatasetList();
		IDataset[] datasets = queryLists(sql, param);
		for (int i=0; i<datasets.length; i++) {
			alldataset.addAll(datasets[i]);
		}
		return alldataset;
	}

    /**
     * get sql by code code
     * @param tabname
     * @param sqlref
     * @return IDataset
     * @throws Exception
     */
    public String getSqlByCodeCode(String sqlref) throws Exception {
    	return getSqlByCodeCode(null, sqlref);
    }
    
    /**
     * get sql by code code
     * @param tabname
     * @param sqlref
     * @return IDataset
     * @throws Exception
     */
    public String getSqlByCodeCode(String tabname, String sqlref) throws Exception {
    	
    	IData codeprm = new DataMap();
    	codeprm.put("TAB_NAME", tabname);
    	codeprm.put("SQL_REF", sqlref);
    	
		SQLParser parser = new SQLParser(codeprm);
		parser.addSQL("select SQL_STMT from CODE_CODE where 1 = 1");
		parser.addSQL(" and DAO_NAME = :DAO_NAME");
		parser.addSQL(" and TAB_NAME = :TAB_NAME");
		parser.addSQL(" and SQL_REF = :SQL_REF");
		
		BaseEntity entity = new BaseEntity(bd, BaseFactory.CENTER_CONNECTION_NAME);
		IDataset dataset = entity.queryList(parser);
		String sqlstmt = dataset.size() == 1 ? ((String) dataset.get(0, "SQL_STMT")).replaceAll(":V", ":") : null;
		if (sqlstmt == null) log.error("CODE_CODE not exist TAB_NAME=" + tabname + " and SQL_REF=" + sqlref + " recode"); 
		
    	
    	return sqlstmt;
    }
    
	/**
	 * query list by code code
	 * @param sqlref
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
    public IDataset queryListByCodeCode(String sqlref, IData param) throws Exception {
    	return queryListByCodeCode(null, sqlref, param);
    }
    
    /**
     * query list by code code
     * @param daoname
     * @param sqlref
     * @param param
     * @return IDataset
     * @throws Exception
     */
    public IDataset queryListByCodeCode(String daoname, String sqlref, IData param) throws Exception {
    	return queryListByCodeCode(daoname, sqlref, param, null);
    }
    
    /**
     * query list by code code
     * @param tabname
     * @param sqlref
     * @param param
     * @param pagination
     * @return IDataset
     * @throws Exception
     */
    public IDataset queryListByCodeCode(String tabname, String sqlref, IData param, Pagination pagination) throws Exception {
		String sqlstmt = getSqlByCodeCode(tabname, sqlref);
    	return sqlstmt == null ? null : queryList(sqlstmt, param, pagination);
    }
    
    /**
     * execute update by CodeCode
     * @param tabname
     * @param sqlref
     * @param param
     * @return
     * @throws Exception
     */
    public int executeUpdateByCodeCode(String tabname, String sqlref, IData param) throws Exception {
    	String sqlstmt = getSqlByCodeCode(tabname, sqlref);
    	return executeUpdate(sqlstmt, param);
    }
    
    /**
     * execute batch by CodeCode
     * @param tabname
     * @param sqlref
     * @param params
     * @return
     * @throws Exception
     */
    public int[] executeBatchByCodeCode(String tabname, String sqlref, IDataset params) throws Exception {
    	String sqlstmt = getSqlByCodeCode(tabname, sqlref);
    	return executeBatch(sqlstmt, params);
    }

    /**
     * query list by code code parser
     * @param tabname
     * @param sqlref
     * @param param
     * @return IDataset
     * @throws Exception
     */
    public IDataset queryListByCodeCodeParser(String tabname, String sqlref, IData param) throws Exception {
    	return queryListByCodeCodeParser(tabname, sqlref, param, null);
    }
    
    /**
     * query list by code code parser
     * @param tabname
     * @param sqlref
     * @param param
     * @param pagination
     * @return IDataset
     * @throws Exception
     */
    public IDataset queryListByCodeCodeParser(String tabname, String sqlref, IData param, Pagination pagination) throws Exception {
		String sqlstmt = getSqlByCodeCode(tabname, sqlref);
		
		if (sqlstmt != null && !"".equals(sqlstmt)) {
			SQLParser parser = new SQLParser(param, "V");
			String[] stmts = sqlstmt.split("\n");
			for (int i=0; i<stmts.length; i++) {
				if (!"".equals(stmts[i])) {
					parser.addSQL(stmts[i] + "\n");
				}
			}
			return queryList(parser, pagination);
		}
		
		return null;
    }
    
    /**
     * get sql by sqlstore
     * @param sqlref
     * @return IDataset
     * @throws Exception
     */
    public String getSqlBySqlstore(String sqlref) throws Exception {
    	BaseEntity entity = new BaseEntity(bd, BaseFactory.CENTER_CONNECTION_NAME);
    	IDataset dataset = entity.queryList("select SQL_STMT from TD_S_SQLSTORE where SQL_REF = ?", new Object[] {sqlref});
    	String sqlstmt = dataset.size() == 1 ? (String) dataset.get(0, "SQL_STMT") : null;
    	if (sqlstmt == null) log.error("TD_S_SQLSTORE not exist SQL_REF=" + sqlref + " recode"); 
		
		return sqlstmt;
    }
    
	/**
	 * query list by sqlstore
	 * @param sqlref
	 * @param param
	 * @return IDataset
	 * @throws Exception
	 */
    public IDataset queryListBySqlstore(String sqlref, IData param) throws Exception {
    	return queryListBySqlstore(sqlref, param, null);
    }
    
    /**
     * query list by sqlstore
     * @param sqlref
     * @param param
     * @param pagination
     * @return IDataset
     * @throws Exception
     */
    public IDataset queryListBySqlstore(String sqlref, IData param, Pagination pagination) throws Exception {
		String sqlstmt = getSqlBySqlstore(sqlref);
    	return sqlstmt == null ? null : queryList(sqlstmt, param, pagination);
    }

    /**
     * query list by sqlstore parser
     * @param sqlref
     * @param param
     * @return IDataset
     * @throws Exception
     */
    public IDataset queryListBySqlstoreParser(String sqlref, IData param) throws Exception {
    	return queryListBySqlstoreParser(sqlref, param, null);
    }
    
    /**
     * query list by sqlstore parser
     * @param sqlref
     * @param param
     * @param pagination
     * @return IDataset
     * @throws Exception
     */
    public IDataset queryListBySqlstoreParser(String sqlref, IData param, Pagination pagination) throws Exception {
		String sqlstmt = getSqlBySqlstore(sqlref);
		
		if (sqlstmt != null && !"".equals(sqlstmt)) {
			SQLParser parser = new SQLParser(param);
			String[] stmts = sqlstmt.split("\n");
			for (int i=0; i<stmts.length; i++) {
				if (!"".equals(stmts[i])) {
					parser.addSQL(stmts[i] + "\n");
				}
			}
			return queryList(parser, pagination);
		}
		
		return null;
    }
    
    /**
     * execute update by sqlstore
     * @param sqlref
     * @param param
     * @return int
     * @throws Exception
     */
    public int executeUpdateBySqlstore(String sqlref, IData param) throws Exception {
		String sqlstmt = getSqlBySqlstore(sqlref);
    	return executeUpdate(sqlstmt, param);
    }
    
    /**
     * execute update by sqlstore
     * @param sqlref
     * @param params
     * @return int[]
     * @throws Exception
     */
    public int[] executeBatchBySqlstore(String sqlref, IDataset params) throws Exception {
		String sqlstmt = getSqlBySqlstore(sqlref);
    	return executeBatch(sqlstmt, params);
    }
	
}